package com.dbc;

import com.vo.Students;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentsDB {
    public int deleteStudents(String studentId) {
        int flag = 0;
        Connection con = null;
        PreparedStatement pStatement=null;
        try {
            con = ConnectionManager.getConnection();
            String sql = "delete from students where studentId=?";
            pStatement = con.prepareStatement(sql);
            pStatement.setString(1,studentId);
            flag = pStatement.executeUpdate();

        }catch (SQLException e){
            e.printStackTrace();
        }finally{
            ConnectionManager.closeStatement(pStatement);
            ConnectionManager.closeConnection(con);
        }
        return flag;
    }

    public List<Students> getAllStudents() {
        ArrayList<Students> allStudents = new ArrayList<Students>();
        Connection con=null;
        PreparedStatement pStatement=null;
        ResultSet res=null;
        try{
            con = ConnectionManager.getConnection();//1.创建连接
            String sql = "select * from students";
            pStatement = con.prepareStatement(sql);//2.创建预处理语句总管
            res = pStatement.executeQuery();//3.语句总管执行查询
            String studentId;
            String studentName;
            String studentPwd;
            String sex;
            int classId;
            while (res.next()) {
                studentId = res.getString(1);
                studentName = res.getString(2);
                studentPwd = res.getString(3);
                sex = res.getString(4);
                classId = res.getInt(5);
                allStudents.add(new Students(studentId,studentName,studentPwd,sex,classId));
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }finally{
            ConnectionManager.closeResultSet(res);//5.关闭结果集 对象、语句总管、连接
            ConnectionManager.closeStatement(pStatement);
            ConnectionManager.closeConnection(con);
        }
        return allStudents;
    }

    public Students getStuById(String studentId) {
        Students stu = null;
        Connection con = null;
        PreparedStatement pStatement=null;
        ResultSet res = null;
        try {
            con = ConnectionManager.getConnection();
            String sql = "select * from students where studentId=?";
            pStatement = con.prepareStatement(sql);
            pStatement.setString(1,studentId);
            res = pStatement.executeQuery();
            if(res.next()){
                String studentName=res.getString(2);
                String studentPwd=res.getString(3);
                String sex=res.getString(4);
                int classId=res.getInt(5);
                stu= new Students(studentId,studentName,studentPwd,sex,classId);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally{
            ConnectionManager.closeResultSet(res);//5.关闭结果集 对象、语句总管、连接
            ConnectionManager.closeStatement(pStatement);
            ConnectionManager.closeConnection(con);
        }
        return stu;
    }

    public List<Students> getStuByLike(String stuId, String stuName, String className) {
        ArrayList<Students> allStudents = new ArrayList<Students>();
        Connection con=null;
        PreparedStatement pStatement=null;
        ResultSet res=null;
        try{
            con = ConnectionManager.getConnection();//1.创建连接
            String sql = "select students.* from students,classes where students.classId=classes.classId and studentId like ? and studentName like ? and className like ?";
            pStatement = con.prepareStatement(sql);//2.创建预处理语句总管
            pStatement.setString(1, "%"+stuId+"%");
            pStatement.setString(2, "%"+stuName+"%");
            pStatement.setString(3, "%"+className+"%");
            res = pStatement.executeQuery();//3.语句总管执行查询
            String studentId;
            String studentName;
            String studentPwd;
            String sex;
            int classId;
            while (res.next()) {
                studentId = res.getString(1);
                studentName = res.getString(2);
                studentPwd = res.getString(3);
                sex = res.getString(4);
                classId = res.getInt(5);
                allStudents.add(new Students(studentId,studentName,studentPwd,sex,classId));
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }finally{
            ConnectionManager.closeResultSet(res);//5.关闭结果集 对象、语句总管、连接
            ConnectionManager.closeStatement(pStatement);
            ConnectionManager.closeConnection(con);
        }
        return allStudents;
    }
    public int insertStudents(Students stu) {
        int flag = 0;
        Connection con = null;
        PreparedStatement pStatement = null;
        try {
            con = ConnectionManager.getConnection();  //1.创建连接
            String strSql = "insert into students values(?,?,?,?,?)";
            pStatement = con.prepareStatement(strSql);  //2.创建预处理语句总管
            pStatement.setString(1, stu.getStudentId());
            pStatement.setString(2, stu.getStudentName());
            pStatement.setString(3, stu.getStudentPwd());
            pStatement.setString(4, stu.getSex());
            pStatement.setInt(5, stu.getClassId());
            flag=pStatement.executeUpdate();  //3.语句总管执行SQL语句
        } catch (SQLException sqlE) {
            sqlE.printStackTrace();
        } finally {
            ConnectionManager.closeStatement(pStatement);//4.关闭语句总管、连接
            ConnectionManager.closeConnection(con);
        }
        return flag;
    }

    public int updateStudents(Students stu) {
        int flag = 0;
        Connection con = null;
        PreparedStatement pStatement = null;
        try {
            con = ConnectionManager.getConnection();  //1.创建连接
            String strSql = "update students set studentName=?,studentPwd=?,sex=?,classId=? where studentId=? ";
            pStatement = con.prepareStatement(strSql);  //2.创建预处理语句总管

            pStatement.setString(1, stu.getStudentName());
            pStatement.setString(2, stu.getStudentPwd());
            pStatement.setString(3, stu.getSex());
            pStatement.setInt(4, stu.getClassId());
            pStatement.setString(5, stu.getStudentId());
            flag=pStatement.executeUpdate();  //3.语句总管执行SQL语句
        } catch (SQLException sqlE) {
            sqlE.printStackTrace();
        } finally {
            ConnectionManager.closeStatement(pStatement);//4.关闭语句总管、连接
            ConnectionManager.closeConnection(con);
        }
        return flag;
    }
}
